package SmartHome.dao.impl;

import SmartHome.dao.FamilyDao;
import SmartHome.entity.FamilyInfo;
import SmartHome.util.JdbcUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * ClassName : FamilyDaoImpl
 * Description :
 * Author : Wang Beijia
 * Date: 2021-07-07 07:59
 */
public class FamilyDaoImpl implements FamilyDao{
    public int executeUpdate(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 1.加载驱动
            // 2.连接数据库
            conn = JdbcUtil.getConn();
            // 3.创建语句
            ps = conn.prepareStatement(sql);
            // 遍历参数
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            // 4.执行语句
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.释放资源
            JdbcUtil.close(conn, ps, null);
        }
        return 0;
    }

    @Override
    public void add(FamilyInfo familyInfo) {
        String sql = "insert into FamilyInfo(familyID,familyName,fPhoneNumber,fState) values (?,?,?,1)";
        this.executeUpdate(sql, familyInfo.getFamilyId(), familyInfo.getFamilyName(),
                familyInfo.getFPhoneNumber());
    }

    @Override
    public void delete(int fId) {
        String sql = "update FamilyInfo set fState=0 where familyID = ?";
        this.executeUpdate(sql, fId);
    }

    @Override
    public FamilyInfo query(int fId) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        String sql = "select * from FamilyInfo where fState=1 and familyId="+fId;
        try {
            conn = JdbcUtil.getConn();
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            if(rs.next()) {
                FamilyInfo Family = new FamilyInfo(rs.getInt(1),
                        rs.getString(2), rs.getInt(3),1);
                return Family;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn,st, rs);
        }
        return null;
    }

    public FamilyInfo query1(int fId) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        String sql = "select * from FamilyInfo where familyId="+fId;
        try {
            conn = JdbcUtil.getConn();
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            if(rs.next()) {
                FamilyInfo Family = new FamilyInfo(rs.getInt(1),
                        rs.getString(2), rs.getInt(3),1);
                return Family;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn,st, rs);
        }
        return null;
    }

    public FamilyInfo query2() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        String sql = "select * from FamilyInfo where fState=0";
        try {
            conn = JdbcUtil.getConn();
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            if(rs.next()) {
                FamilyInfo Family = new FamilyInfo(rs.getInt(1),
                        rs.getString(2), rs.getInt(3),1);
                return Family;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn,st, rs);
        }
        return null;
    }

    @Override
    public void update(int uId, FamilyInfo familyInfo) {
        String sql = "update FamilyInfo set familyId=?, familyName=?,fPhoneNumber=?,fState=? where familyId =? ";
        this.executeUpdate(sql, familyInfo.getFamilyId(), familyInfo.getFamilyName(), familyInfo.getFPhoneNumber(),1, uId);
    }

    public List<FamilyInfo> getAll() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConn();
            st = conn.createStatement();
            String sql = "select * from FamilyInfo where fState=1";
            System.out.println(sql);
            rs = st.executeQuery(sql);
            List<FamilyInfo> list = new ArrayList<FamilyInfo>();

            while(rs.next()) {
                FamilyInfo Family = new FamilyInfo(rs.getInt(1), rs.getString("familyName"),rs.getInt("fPhoneNumber"),1);
                list.add(Family);
            }
            System.out.println(list);
            return list;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, st, rs);
        }
        return null;
    }
    
}
